﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
namespace StreetSmartAdmin
{
    public partial class ExportConverted : System.Web.UI.Page
    {
        string strsql = "select DateConverted as 'Date Converted', OperatorSSE as 'Operator SSE', SourceCode as 'Source Code', VendorID as 'Vendor ID', RESI_SME as 'RESI/SME', ElectricityConversion as 'Electricity Conversion?', GasConversion as 'Gas Conversion?', ElecTotalAnnualSpend as 'Annual Electricity Spend', GasTotalAnnualSpend as 'Annual Gas Spend', MoverSwicher as 'Mover/Swicher', MoveInDate as 'Move In Date', CompanyName as 'Company Name', CompanyABN as 'Company ABN', FirstAccountHolderTitle as 'First Account Holder Title',  FirstAccountHolderFirstName as 'First Account Holder First Name', FirstAccountHolderSurname as 'First Account Holder Surname', FirstAccountHolderDOB as 'First Account Holder DOB', ID_Number as 'ID Number', ID_Expiry as 'ID Expiry', ID_Type as 'ID Type', SecondAccountHolderTitle as 'Second Account Holder Title', SecondAccountHolderFirstName as 'Second Account Holder First Name', SecondAccountHolderSurname as 'Second Account Holder Surname', SecondAccountHolderDOB as 'Second Account Holder DOB', ContactTel as 'Contact Tel', SecondTel as 'SecondTel', Email, SupplyAddress as 'Supply Address', SupplyAddressSuburb as 'Supply Address Suburb', SupplyAddressState as 'Supply Address State', SupplyAddressPostcode as 'Supply Address Postcode', TenantOwner as 'Tenant/Owner', Elec, Gas, ProductType as 'Product Type', NoOfPeopleInHouse as 'No Of People In House', ConcessionCardType as 'Concession Card Type', ConcessionCardEntitlement as 'Concession Card Entitlement', ConcessionCardNo as 'Concession Card No', ConcessionCardExp as 'Concession Card Exp', NameOnConcessionCard as 'Name On Concession Card', NMI, MIRN, AnyHazards as 'Any Hazards?', AnyAccessRequirements as 'Any Access Requirements?', SpecialInstructionsForAccess as 'Special Instructions For Access?', AdvisedMainSwitchNeedsTurningOff as 'Advised Main Switch Needs Turning Off?', SafetyCertificateRequired as 'Safety Certificate Required?', ElecStatus as 'Elec Status', GasStatus as 'Gas Status', Reason, TRUenergyComments as 'TRUenergy Comments', ExistingTRUEnergyCustomer as 'Existing TRUEnergy Customer?' from tblUser ";
        protected string connectstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ConnectionString;

        protected void Page_Load()
        {
            //((Label)Header.FindControl("lblUser")).Text = ((Userinfo)Session["UserInfo"]).fullname; 
            //((Label)Header.FindControl("lblUser")).Text = ((String)Session["UserName"]);
            ADODotNetCRUD crud = new ADODotNetCRUD();
            Userinfo user = crud.GetUser(User.Identity.Name);
            ((Label)Header.FindControl("lblUser")).Text = (user.fullname);
            ((Label)Header.FindControl("lblGroup")).Text = (user.groupname);
            String date = DateTime.Now.Date.ToString("yyyy-MM-dd ");
            TextBox1.Text = date;
            //System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"];
            //SqlDataSource3.ConnectionString = connString.ConnectionString;
            //SqlDataSource3.SelectCommand = "select DateConverted as 'Date Converted', OperatorSSE as 'Operator SSE', SourceCode as 'Source Code', VendorID as 'Vendor ID', RESI_SME as 'RESI/SME', ElectricityConversion as 'Electricity Conversion?', GasConversion as 'Gas Conversion?', ElecTotalAnnualSpend as 'Annual Electricity Spend', GasTotalAnnualSpend as 'Annual Gas Spend', MoverSwicher as 'Mover/Swicher', MoveInDate as 'Move In Date', CompanyName as 'Company Name', CompanyABN as 'Company ABN', FirstAccountHolderTitle as 'First Account Holder Title',  FirstAccountHolderFirstName as 'First Account Holder First Name', FirstAccountHolderSurname as 'First Account Holder Surname', FirstAccountHolderDOB as 'First Account Holder DOB', ID_Number as 'ID Number', ID_Expiry as 'ID Expiry', ID_Type as 'ID Type', SecondAccountHolderTitle as 'Second Account Holder Title', SecondAccountHolderFirstName as 'Second Account Holder First Name', SecondAccountHolderSurname as 'Second Account Holder Surname', SecondAccountHolderDOB as 'Second Account Holder DOB', ContactTel as 'Contact Tel', SecondTel as 'SecondTel', Email, SupplyAddress as 'Supply Address', SupplyAddressSuburb as 'Supply Address Suburb', SupplyAddressState as 'Supply Address State', SupplyAddressPostcode as 'Supply Address Postcode', TenantOwner as 'Tenant/Owner', Elec, Gas, ProductType as 'Product Type', NoOfPeopleInHouse as 'No Of People In House', ConcessionCardType as 'Concession Card Type', ConcessionCardEntitlement as 'Concession Card Entitlement', ConcessionCardNo as 'Concession Card No', ConcessionCardExp as 'Concession Card Exp', NameOnConcessionCard as 'Name On Concession Card', NMI, MIRN, AnyHazards as 'Any Hazards?', AnyAccessRequirements as 'Any Access Requirements?', SpecialInstructionsForAccess as 'Special Instructions For Access?', AdvisedMainSwitchNeedsTurningOff as 'Advised Main Switch Needs Turning Off?', SafetyCertificateRequired as 'Safety Certificate Required?', ElecStatus as 'Elec Status', GasStatus as 'Gas Status', Reason, TRUenergyComments as 'TRUenergy Comments', ExistingTRUEnergyCustomer as 'Existing TRUEnergy Customer?', SSE_Operator_Feedback as 'SSE Operator Feedback', SSE_Operator_Feedback as 'SSE Operator Feedback' from tblUser where DateConverted = '"+date+"';";
            //GridView1.DataSourceID = "SqlDataSource3";
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            String querySql = strsql + "where DateConverted = '" + TextBox1.Text + "' order by DateConverted;";
            SqlConnection SqlConnection = new SqlConnection();
            SqlConnection.ConnectionString = connectstring;
            SqlCommand command = new SqlCommand(querySql, SqlConnection);
            SqlConnection.Open();
            DataTable dt = GetDataTable(command);
            StringWriter sw = new StringWriter();
            Export_Excel(dt, sw);  
            Response.AddHeader("Content-Disposition", "attachment; filename=Converted.xls");
            Response.ContentType = "application/ms-excel";
            //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.Write(sw);
            Response.End();
            SqlConnection.Close();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            String querySql = strsql + "where DateConverted >= '" + TextBox2.Text + "'and DateConverted <= '" + TextBox3.Text + "' order by DateConverted;";
            SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
            SqlConnection.ConnectionString = connectstring;
            SqlCommand command = new SqlCommand(querySql, SqlConnection);
            SqlConnection.Open();
            DataTable dt = GetDataTable(command);
            StringWriter sw = new StringWriter();
            Export_Excel(dt, sw);
            Response.AddHeader("Content-Disposition", "attachment; filename=Converted.xls");
            Response.ContentType = "application/ms-excel";
            //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.Write(sw);
            Response.End();
            SqlConnection.Close();
        }

        public static DataTable GetDataTable(System.Data.SqlClient.SqlCommand cmd)
        {

                // create a new data adapter based on the specified query.
                SqlDataAdapter da = new SqlDataAdapter();
                //set the SelectCommand of the adapter
                da.SelectCommand = cmd;
                // create a new DataTable
                DataTable dtGet = new DataTable();
                //fill the DataTable
                da.Fill(dtGet);
                //return the DataTable
                return dtGet;
        }

        public void Export_Excel(DataTable dt, StringWriter w)
        {
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write('\t');
                }
                w.Write('\n');
                object[] values = new object[dt.Columns.Count];
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        w.Write(values[i]);
                        w.Write('\t');
                    }
                    w.Write('\n');
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            if (DropDownList2.Text.Equals(""))
            {
                GridView1.DataSourceID = "SqlDataSource2";
            }
            else
            {
                GridView1.DataSourceID = "SqlDataSource9";
            }
            GridView1.DataBind();
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            Response.Clear();
            Response.AppendHeader("Content-Disposition", "attachment;filename= " + Server.UrlEncode("Export.xls"));
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("EN", true);
            System.IO.StringWriter stringWrite = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
            GridView1.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();
            GridView1.AllowPaging = true;
        }

        override public void VerifyRenderingInServerForm(System.Web.UI.Control control)
        { }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridView1.PageSize = Convert.ToInt32(DropDownList1.SelectedValue);
        }

        protected void UpdateButton_Click(object sender, EventArgs e)
        {
            String miss = "";
            if (((TextBox)DetailsView1.FindControl("AccountStatus")).Text.Equals("9") || ((TextBox)DetailsView1.FindControl("StatusName")).Text.Equals("Converted"))
            {
                if (((TextBox)DetailsView1.FindControl("TypeOrg")).Text.Equals("RESI"))
                {
                    if (((TextBox)DetailsView1.FindControl("FirstName")).Text.Equals(""))
                        miss = miss + "FirstName \\n";
                    if (((TextBox)DetailsView1.FindControl("LastName")).Text.Equals(""))
                        miss = miss + "LastName \\n";
                    if (((TextBox)DetailsView1.FindControl("Email")).Text.Equals(""))
                        miss = miss + "Email \\n";
                    if (((TextBox)DetailsView1.FindControl("Street")).Text.Equals(""))
                        miss = miss + "Street \\n";
                    if (((TextBox)DetailsView1.FindControl("Suburb")).Text.Equals(""))
                        miss = miss + "Suburb \\n";
                    if (((TextBox)DetailsView1.FindControl("StateName")).Text.Equals(""))
                        miss = miss + "StateName \\n";
                    if (((TextBox)DetailsView1.FindControl("Postcode")).Text.Equals(""))
                        miss = miss + "Postcode \\n";
                    if (((TextBox)DetailsView1.FindControl("Phone")).Text.Equals(""))
                        miss = miss + "Phone \\n";
                    if (((TextBox)DetailsView1.FindControl("CurrentProvider")).Text.Equals(""))
                        miss = miss + "CurrentProvider \\n";
                    if (((TextBox)DetailsView1.FindControl("PromotionCode")).Text.Equals(""))
                        miss = miss + "PromotionCode \\n";
                    if (((TextBox)DetailsView1.FindControl("LeadSource")).Text.Equals(""))
                        miss = miss + "LeadSource \\n";
                    if (((TextBox)DetailsView1.FindControl("TotalSites")).Text.Equals(""))
                        miss = miss + "TotalSites \\n";
                    if (((TextBox)DetailsView1.FindControl("SourceCode")).Text.Equals(""))
                        miss = miss + "SourceCode \\n";
                    if (((TextBox)DetailsView1.FindControl("ProductType")).Text.Equals(""))
                        miss = miss + "ProductType \\n";
                    if (((TextBox)DetailsView1.FindControl("OperatorSSE")).Text.Equals(""))
                        miss = miss + "OperatorSSE \\n";
                    if (((TextBox)DetailsView1.FindControl("DateConverted")).Text.Equals(""))
                        miss = miss + "DateConverted \\n";
                    if (((TextBox)DetailsView1.FindControl("MoverSwicher")).Text.Equals(""))
                        miss = miss + "MoverSwicher \\n";
                    if (((TextBox)DetailsView1.FindControl("MoverSwicher")).Text.Equals("Mover"))
                    {
                        if (((TextBox)DetailsView1.FindControl("MoveInDate")).Text.Equals(""))
                            miss = miss + "MoveInDate \\n";
                    }
                    if (((TextBox)DetailsView1.FindControl("RESI_SME")).Text.Equals(""))
                        miss = miss + "RESI_SME \\n";
                    if (((TextBox)DetailsView1.FindControl("ElectricityConversion")).Text.Equals(""))
                        miss = miss + "ElectricityConversion \\n";
                    if (((TextBox)DetailsView1.FindControl("GasConversion")).Text.Equals(""))
                        miss = miss + "GasConversion \\n";
                    if (((TextBox)DetailsView1.FindControl("ID_Type")).Text.Equals(""))
                        miss = miss + "ID_Type \\n";
                    if (((TextBox)DetailsView1.FindControl("ID_Number")).Text.Equals(""))
                        miss = miss + "ID_Number \\n";
                    if (((TextBox)DetailsView1.FindControl("FirstAccountHolderTitle")).Text.Equals(""))
                        miss = miss + "FirstAccountHolderTitle \\n";
                    if (((TextBox)DetailsView1.FindControl("FirstAccountHolderFirstName")).Text.Equals(""))
                        miss = miss + "FirstAccountHolderFirstName \\n";
                    if (((TextBox)DetailsView1.FindControl("FirstAccountHolderSurname")).Text.Equals(""))
                        miss = miss + "FirstAccountHolderSurname \\n";
                    if (((TextBox)DetailsView1.FindControl("FirstAccountHolderDOB")).Text.Equals(""))
                        miss = miss + "FirstAccountHolderDOB \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddress")).Text.Equals(""))
                        miss = miss + "SupplyAddress \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressSuburb")).Text.Equals(""))
                        miss = miss + "SupplyAddressSuburb \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressState")).Text.Equals(""))
                        miss = miss + "SupplyAddressState \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressPostcode")).Text.Equals(""))
                        miss = miss + "SupplyAddressPostcode \\n";
                    if (((TextBox)DetailsView1.FindControl("TenantOwner")).Text.Equals(""))
                        miss = miss + "TenantOwner \\n";
                    if (((TextBox)DetailsView1.FindControl("Elec")).Text.Equals(""))
                        miss = miss + "Elec \\n";
                    if (((TextBox)DetailsView1.FindControl("Gas")).Text.Equals(""))
                        miss = miss + "Gas \\n";
                    if (((TextBox)DetailsView1.FindControl("ExistingTRUEnergyCustomer")).Text.Equals(""))
                        miss = miss + "ExistingTRUEnergyCustomer \\n";

                    if (miss.Equals(""))
                        DetailsView1.UpdateItem(false);
                    else
                    {
                        MessageBox("The following fields must be completed:\\n" + miss);
                    }
                }

                if (((TextBox)DetailsView1.FindControl("TypeOrg")).Text.Equals("SME"))
                {
                    if (((TextBox)DetailsView1.FindControl("CompanyName")).Text.Equals(""))
                        miss = miss + "CompanyName \\n";
                    if (((TextBox)DetailsView1.FindControl("CompanyABN")).Text.Equals(""))
                        miss = miss + "CompanyABN \\n";
                    if (((TextBox)DetailsView1.FindControl("FirstName")).Text.Equals(""))
                        miss = miss + "FirstName \\n";
                    if (((TextBox)DetailsView1.FindControl("LastName")).Text.Equals(""))
                        miss = miss + "LastName \\n";
                    if (((TextBox)DetailsView1.FindControl("Email")).Text.Equals(""))
                        miss = miss + "Email \\n";
                    if (((TextBox)DetailsView1.FindControl("Street")).Text.Equals(""))
                        miss = miss + "Street \\n";
                    if (((TextBox)DetailsView1.FindControl("Suburb")).Text.Equals(""))
                        miss = miss + "Suburb \\n";
                    if (((TextBox)DetailsView1.FindControl("StateName")).Text.Equals(""))
                        miss = miss + "StateName \\n";
                    if (((TextBox)DetailsView1.FindControl("Postcode")).Text.Equals(""))
                        miss = miss + "Postcode \\n";
                    if (((TextBox)DetailsView1.FindControl("Phone")).Text.Equals(""))
                        miss = miss + "Phone \\n";
                    if (((TextBox)DetailsView1.FindControl("CurrentProvider")).Text.Equals(""))
                        miss = miss + "CurrentProvider \\n";
                    if (((TextBox)DetailsView1.FindControl("PromotionCode")).Text.Equals(""))
                        miss = miss + "PromotionCode \\n";
                    if (((TextBox)DetailsView1.FindControl("LeadSource")).Text.Equals(""))
                        miss = miss + "LeadSource \\n";
                    if (((TextBox)DetailsView1.FindControl("TotalSites")).Text.Equals(""))
                        miss = miss + "TotalSites \\n";
                    if (((TextBox)DetailsView1.FindControl("SourceCode")).Text.Equals(""))
                        miss = miss + "SourceCode \\n";
                    if (((TextBox)DetailsView1.FindControl("ProductType")).Text.Equals(""))
                        miss = miss + "ProductType \\n";
                    if (((TextBox)DetailsView1.FindControl("OperatorSSE")).Text.Equals(""))
                        miss = miss + "OperatorSSE \\n";
                    if (((TextBox)DetailsView1.FindControl("DateConverted")).Text.Equals(""))
                        miss = miss + "DateConverted \\n";
                    if (((TextBox)DetailsView1.FindControl("MoverSwicher")).Text.Equals(""))
                        miss = miss + "MoverSwicher \\n";
                    if (((TextBox)DetailsView1.FindControl("MoverSwicher")).Text.Equals("Mover"))
                    {
                        if (((TextBox)DetailsView1.FindControl("MoveInDate")).Text.Equals(""))
                            miss = miss + "MoveInDate \\n";
                    }
                    if (((TextBox)DetailsView1.FindControl("RESI_SME")).Text.Equals(""))
                        miss = miss + "RESI_SME \\n";
                    if (((TextBox)DetailsView1.FindControl("ElectricityConversion")).Text.Equals(""))
                        miss = miss + "ElectricityConversion \\n";
                    if (((TextBox)DetailsView1.FindControl("GasConversion")).Text.Equals(""))
                        miss = miss + "GasConversion \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddress")).Text.Equals(""))
                        miss = miss + "SupplyAddress \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressSuburb")).Text.Equals(""))
                        miss = miss + "SupplyAddressSuburb \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressState")).Text.Equals(""))
                        miss = miss + "SupplyAddressState \\n";
                    if (((TextBox)DetailsView1.FindControl("SupplyAddressPostcode")).Text.Equals(""))
                        miss = miss + "SupplyAddressPostcode \\n";
                    if (((TextBox)DetailsView1.FindControl("TenantOwner")).Text.Equals(""))
                        miss = miss + "TenantOwner \\n";
                    if (((TextBox)DetailsView1.FindControl("Elec")).Text.Equals(""))
                        miss = miss + "Elec \\n";
                    if (((TextBox)DetailsView1.FindControl("Gas")).Text.Equals(""))
                        miss = miss + "Gas \\n";
                    if (((TextBox)DetailsView1.FindControl("ExistingTRUEnergyCustomer")).Text.Equals(""))
                        miss = miss + "ExistingTRUEnergyCustomer \\n";

                    if (miss.Equals(""))
                        DetailsView1.UpdateItem(false);
                    else
                    {
                        MessageBox("The following fields must be completed:\\n" + miss);
                    }
                }
            }
            else
                DetailsView1.UpdateItem(false);
        }

        private void MessageBox(string message)
        {
            if (!string.IsNullOrEmpty(message))
            {
                this.ClientScript.RegisterClientScriptBlock(this.GetType(), "alt1", "alert('" + message + "');", true);
            }
        }

    }
}